use [Tfs_Warehouse]

DECLARE @ProjectGuid as varchar(36)
SET @ProjectGuid = (SELECT ProjectNodeGUID FROM GetProjectNodeInfoFromReportFolder('/TfsReports/DefaultCollection/RTM')) 
--SELECT @ProjectGuid

-- TeamProjectCollection - Needed because links are done at the TPC level
DECLARE @TeamProjectCollectionSK int; 
SET @TeamProjectCollectionSK = (SELECT ParentNodeSK FROM DimTeamProject WHERE ProjectNodeGUID = @ProjectGuid );

-- Link Types
DECLARE @ParentWorkItemLinkTypeSK int; 
SET @ParentWorkItemLinkTypeSK = (SELECT [WorkItemLinkTypeSK] FROM [DimWorkItemLinkType] WHERE [LinkID] < 0 AND [ReferenceName] = 'System.LinkTypes.Hierarchy' AND TeamProjectCollectionSK = @TeamProjectCollectionSK);
--SELECT @ParentWorkItemLinkTypeSK

DECLARE @ChildWorkItemLinkTypeSK int; 
SET @ChildWorkItemLinkTypeSK = (SELECT [WorkItemLinkTypeSK] FROM [DimWorkItemLinkType] WHERE [LinkID] > 0 AND [ReferenceName] = 'System.LinkTypes.Hierarchy' AND TeamProjectCollectionSK = @TeamProjectCollectionSK);
--SELECT @ChildWorkItemLinkTypeSK

DECLARE @TestedByLinkTypeSK int;
SET @TestedByLinkTypeSK = (SELECT [WorkItemLinkTypeSK] FROM [DimWorkItemLinkType] WHERE [LinkID] > 0 AND [ReferenceName] = 'Microsoft.VSTS.Common.TestedBy' AND TeamProjectCollectionSK = @TeamProjectCollectionSK);
SELECT @TestedByLinkTypeSK

DECLARE @TestsLinkTypeSK int;
SET @TestsLinkTypeSK = (SELECT [WorkItemLinkTypeSK] FROM [DimWorkItemLinkType] WHERE [LinkID] < 0 AND [ReferenceName] = 'Microsoft.VSTS.Common.TestedBy' AND TeamProjectCollectionSK = @TeamProjectCollectionSK);
--SELECT @TestsLinkTypeSK

SELECT * FROM FactWorkItemLinkHistory where SourceWorkItemID = 71

--SELECT * FROM [DimWorkItemLinkType]

DECLARE @DeliverableCategory as varchar(256)
SET @DeliverableCategory = 'Requirement'

	--SELECT cwi.[System_Id] AS ID FROM [CurrentWorkItemView] cwi
	--	WHERE cwi.[System_WorkItemType] IN (@DeliverableCategory)
	--		AND cwi.[ProjectNodeGUID] = @ProjectGuid
			
	SELECT lh.SourceWorkItemID AS ID FROM FactWorkItemLinkHistory lh
		WHERE lh.WorkItemLinkTypeSK = @ParentWorkItemLinkTypeSK
			AND lh.RemovedDate = CONVERT(DATETIME, '9999', 126)
			AND lh.TeamProjectCollectionSK = @TeamProjectCollectionSK

	union all
				
	SELECT lh.TargetWorkItemID AS ID FROM FactWorkItemLinkHistory lh
	--SELECT * FROM FactWorkItemLinkHistory lh
		WHERE lh.WorkItemLinkTypeSK = @TestsLinkTypeSK
			AND lh.RemovedDate = CONVERT(DATETIME, '9999', 126)
			AND lh.TeamProjectCollectionSK = @TeamProjectCollectionSK
			
			
